#!/usr/bin/python3.8.9
# -*- coding: utf-8 -*-
import datetime
import random
import threading
import time
from queue import Queue

import pymysql
from dbutils.pooled_db import PooledDB

from lingzhu.tools import get_run_time


class ThreadInsert(object):
    "多线程并发MySQL插入数据"

    def __init__(self):
        start_time = time.time()
        self.pool = self.mysql_connection()
        self.data = self.get_data()
        self.task()
        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def mysql_connection(self):
        maxconnections = 15  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='localhost',
            user='root',
            port=3306,
            passwd='123456',
            db='cifdb',
            use_unicode=True)
        return pool

    def get_data(self):
        st = time.time()
        data = []
        for n in range(0, 500000):  # 最佳：50W , 按10w拆分
            line = self.sigle_register_data(n)
            data.append(line)
        n = 50000  # 按每5万行数据为最小单位拆分成嵌套列表
        result = [data[i:i + n] for i in range(0, len(data), n)]
        print(f"共获取{len(result)}组数据,每组{n}个元素.==>> 耗时:{round(time.time() - st, 3)}'s")
        return result

    def sigle_register_data(self, i):
        data_id = f"{datetime.date.today().strftime('%Y%m%d')}" \
                  f"{'%012d' % (i + 1)}"
        customer_name = 'Tina插入数据'
        mobile_phone = "1" + ''.join(random.choice('0123456789') for i in range(10))
        apply_loan_result = 'PRE'
        apply_bt_result = 'PRE'
        apply_consume_result = 'PRE'
        bank_depository_status = 'UNREGISTERED'
        modify_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        create_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        issue_channel = 'ANDROID'
        regist_channel = 'ANDROID'
        risk_level = 'A'
        status = 'OPEN'
        depository_bind_card_status = 'NOBINDCARD'

        tuple_data = (data_id, customer_name, mobile_phone, apply_loan_result, apply_bt_result, apply_consume_result,
                      bank_depository_status, modify_date, create_date, issue_channel, regist_channel, risk_level,
                      status, depository_bind_card_status)

        return tuple_data

    def mysql_insert(self, *args):
        con = self.pool.connection()
        cur = con.cursor()
        sql = 'INSERT INTO `t_customer_base` (id,customer_name,mobile_phone,apply_loan_result,apply_bt_result,' \
              'apply_consume_result,bank_depository_status,modify_date,create_date,issue_channel,regist_channel,' \
              'risk_level,`STATUS`,depository_bind_card_status)values' \
              '( % s, % s, % s, % s, % s, % s, % s, % s, % s, % s, % s, % s, % s, % s);'
        try:
            cur.executemany(sql, *args)
            con.commit()
        except Exception as e:
            con.rollback()  # 事务回滚
            print('SQL执行有误,原因:', e)
        finally:
            cur.close()
            con.close()

    def task(self):
        q = Queue(maxsize=10)  # 设定最大队列数和线程数
        st = time.time()
        while self.data:
            content = self.data.pop()
            t = threading.Thread(target=self.mysql_insert, args=(content,))
            q.put(t)
            if (q.full() == True) or (len(self.data)) == 0:
                thread_list = []
                while q.empty() == False:
                    t = q.get()
                    thread_list.append(t)
                    t.start()
                for t in thread_list:
                    t.join()
        print("数据插入完成.==>> 耗时:{}'s".format(round(time.time() - st, 3)))


if __name__ == '__main__':
    start_time = time.time()
    for i in range(1):
        ThreadInsert()
        print(f'**********************************************************执行了{i + 1}次！')
    end_time = time.time()
    get_run_time.get_run_time(end_time, start_time)
